#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb
import os
 
import hashlib

#DB parameter


def update360buyRepu():
    conn = getConnect()
    qPage = MySQLQueryPagination(conn=conn, numPerPage=2000)
    sqlStr = r'SELECT id,repu FROM `prod_base_info_3c` where site_id=6'
    for result in qPage.queryForList(sql=sqlStr):
        prodList = []
        for prod in result:
            id = prod[0]
            repu = prod[1]
            repu = repu * 5 / 100
            print repu, id
            prodList.append((repu, id))
        
        print '+++'
        batchUpdateProdBaseInfo(conn, prodList)
    
    conn.close()

strHost = 'localhost'
strDB = 'bigo_db_new'
strUser = 'root'
strPasswd = ''

def seEncode(ustr, encoding='utf-8'):
    '''负责把入数据库的字符串，转化成utf-8编码'''
    if ustr is None:
        return ''
    if isinstance(ustr, unicode):
        return ustr.encode(encoding, 'ignore')
    elif isinstance(ustr, (list,tuple,set)):
        return '[%s]' % ','.join([seEncode(s,encoding) for s in ustr])        
    return str(ustr)

#connect to DB
def getConnect(db=strDB, host=strHost, user=strUser, passwd=strPasswd, charset="utf8"):
    return MySQLdb.connect(host=strHost, db=strDB, user=strUser, passwd=strPasswd, charset="utf8")

def initClientEncode(conn):
    '''mysql client encoding=utf8'''
    curs = conn.cursor()
    curs.execute("SET NAMES utf8")
    conn.commit()
    return curs

class MySQLQueryPagination(object):
    '''MySQL 分页类的实现'''
    def __init__(self,conn,numPerPage = 20):
        self.conn = conn
        self.numPerPage = numPerPage
        
    def queryForList(self,sql,param = None):
        totalPageNum = self.__calTotalPages(sql,param)
        for pageIndex in xrange(totalPageNum):
            yield self.__queryEachPage(sql,pageIndex,param)
            
    def __createPaginaionQuerySql(self,sql,currentPageIndex):
        startIndex = self.__calStartIndex(currentPageIndex)
        qSql  = r'select * from (%s) total_table limit %s,%s' % (sql,startIndex,self.numPerPage)
        return qSql
    
    def __queryEachPage(self,sql,currentPageIndex,param = None):
        curs = initClientEncode(self.conn) 
        qSql = self.__createPaginaionQuerySql(sql, currentPageIndex)      
        if param is None:
            curs.execute(qSql)
        else:
            curs.execute(qSql,param)
            
        result = curs.fetchall()
        curs.close()
        return result
        
    def __calStartIndex(self,currentPageIndex):
        startIndex = currentPageIndex  * self.numPerPage;
        return startIndex;
    
    def __calTotalRowsNum(self,sql,param = None):
        ''' 计算总行数 '''
        tSql = r'select count(*) from (%s) total_table' % sql
        curs = initClientEncode(self.conn) 
        if param is None:
            curs.execute(tSql)
        else:
            curs.execute(tSql,param)
        result = curs.fetchone()
        curs.close()
        totalRowsNum = 0
        if result != None:
            totalRowsNum = int(result[0])
        return totalRowsNum
    
    def __calTotalPages(self,sql,param):
        ''' 计算总页数 '''
        totalRowsNum = self.__calTotalRowsNum(sql,param)
        totalPages = 0;
        if (totalRowsNum % self.numPerPage) == 0:
            totalPages = totalRowsNum / self.numPerPage;
        else:
            totalPages = (totalRowsNum / self.numPerPage) + 1 
        return totalPages
    
    def __calLastIndex(self, totalRows, totalPages,currentPageIndex):
        '''计算结束时候的索引'''
        lastIndex = 0;
        if totalRows < self.numPerPage:
            lastIndex = totalRows;
        elif ((totalRows % self.numPerPage == 0)
                or (totalRows % self.numPerPage != 0 and currentPageIndex < totalPages)) :
            lastIndex = currentPageIndex * self.numPerPage
        elif (totalRows % self.numPerPage != 0 and currentPageIndex == totalPages): # 最后一页
            lastIndex = totalRows         
        return lastIndex

#===============================================================================
#  表 `websit_base_info` db 操作
#===============================================================================
def getAllWebsiteBaseInfo():
    '''获取所有的站点的en_name与id信息'''
    conn = getConnect()
    curs = initClientEncode(conn) 
    sqlStr = 'SELECT en_name,id FROM `websit_base_info` ' #生成sql语句
    curs.execute(sqlStr)
    result = curs.fetchall()
    curs.close()
    conn.close()
    return result

#===============================================================================
#  表 `cat_base_config` db 操作
#===============================================================================
def getAllCatBaseConfig():
    '''获取所有的站点的en_name与id信息'''
    conn = getConnect()
    curs = initClientEncode(conn) 
    sqlStr = '''SELECT main_cat_id, baseinfo_table_name, priceinfo_cur_table_name, priceinfo_his_table_name,
                                   en_name  FROM `cat_base_config`''' #生成sql语句
    curs.execute(sqlStr)
    result = curs.fetchall()
    curs.close()
    conn.close()
    return result

#===============================================================================
# 表 `prod_catagory` db proc
#===============================================================================
def saveProdCat(rawCatId, siteId, parentId, url, name, parentPath,
                 level, self_cat_id = 0,cat_base_id = 0  ):
    ''' 保存各站点的分类信息 '''
    conn = getConnect()
    curs = initClientEncode(conn)
    if parentId == '':
        print url
    sqlStr = '''INSERT INTO `prod_catagory` (`raw_cat_id` ,`site_id` ,`parent_id` ,`url` ,`name` ,`parent_path` ,`level` , self_cat_id, cat_base_id,`update_time` )
                             VALUES ( %s, %s, %s, %s, %s, %s, %s, now()) '''
    param = [seEncode(pt) for pt in (rawCatId, siteId, parentId, url, name, parentPath, level,self_cat_id, cat_base_id,)]
    curs.execute(sqlStr, param)
    curs.close()
    conn.close()
    return int(curs.lastrowid) 

def match55bigoCats(site_id,name):
    '''获取所有的站点的en_name与id信息'''
    conn = getConnect()
    curs = initClientEncode(conn) 
    sqlStrPattern = '''SELECT id,site_id,self_cat_id,name,url,cat_base_id   FROM `prod_catagory` where (site_id =9 or site_id=%s) AND LEVEL =3 AND name LIKE '%%{name}%%' ''' #生成sql语句
    sqlStr = sqlStrPattern.format(name = name)
    param = [site_id]
    curs.execute(sqlStr,param)
    result = curs.fetchall()
    curs.close()
    conn.close()
    return result
 
def getCatIdFromRawCatID(raw_cat_id, site_id):
    '''获取分类id'''
    conn = getConnect()
    curs = initClientEncode(conn) 
    sqlStr = 'SELECT id,self_cat_id,cat_base_id FROM `prod_catagory` where raw_cat_id = %s and site_id = %s'
    param = (raw_cat_id, site_id)
    curs.execute(sqlStr, param)
    result = curs.fetchone()
    curs.close()
    conn.close()
    if result != None:
        return result
    
def getCatBySiteIdAndLevel(site_id,level):
    '''获取分类id'''
    conn = getConnect()
    curs = initClientEncode(conn) 
    sqlStr = 'SELECT id ,name,parent_path FROM `prod_catagory` where site_id = %s and level = %s'
    param = ( site_id,level)
    curs.execute(sqlStr, param)
    result = curs.fetchall()
    curs.close()
    conn.close()
    return result

#===============================================================================
# 表 `prod_base_info` db proc
#===============================================================================
def saveProdBaseInfo(conn,site_id , raw_id , name , url , img_url , repu, eval_num, cat_id,u_time):
    '''保存商品基本信息到表：prod_base_info'''
    curs = initClientEncode(conn)
    sqlStr = '''INSERT INTO `prod_base_info` (`site_id` ,`raw_id` ,`name` ,`url` ,`img_url` , `repu`, `eval_num`, `cat_id` ,`u_time` )
                                                         VALUES (%s , %s, %s, %s, %s, %s , %s, %s , %s)'''
    param = [seEncode(pt) for pt in (site_id , raw_id , name , url , img_url , repu, eval_num, cat_id,u_time)]
    curs.execute(sqlStr, param)
    conn.commit()
    ret = curs.lastrowid
    curs.close()
    return int(ret) 

def batchSaveProdBaseInfo(conn, params):
    '''批量保存商品基本信息到表：prod_base_info'''
    curs = initClientEncode(conn)
    sqlStr = '''INSERT INTO `prod_base_info` (`site_id` ,`raw_id` ,`name` ,`url` ,`img_url` , `repu`, `eval_num`, `cat_id` ,`u_time` )
                                                         VALUES (%s , %s, %s, %s, %s, %s , %s, %s , %s)'''
    wparams = list()
    for param in params:
        wparams.append([seEncode(pt) for pt in param])
    curs.executemany(sqlStr, wparams)
    conn.commit()
    curs.close() 

def getProdId(conn, site_id, raw_id):
    ''' 获取prod_id '''
    curs = initClientEncode(conn) 
    sqlStr = 'SELECT id FROM `prod_base_info` where site_id = %s and raw_id = %s'
    param = (site_id, raw_id)
    curs.execute(sqlStr, param)
    result = curs.fetchone()
    curs.close()
    if result != None:
        return result[0]
    
def getAllRawProdIdsBySite(site_id):
    '''获取某一个站点的所有prod_id'''
    conn = getConnect()
    curs = initClientEncode(conn) 
    sqlStr = 'SELECT raw_id,id FROM `prod_base_info` where site_id = %s'
    param = (site_id)
    curs.execute(sqlStr, param)
    result = curs.fetchall()
    curs.close()
    conn.close()
    return result

#===============================================================================
# 表 `prod_price_info` db proc
#===============================================================================

def saveProdPriceInfo(prod_id, real_price, cur_price, diff_price, adwords, coupon=0.00, ex_gift='',
                          order_cut=0.00, crash_cut=0.00, m_price=0.00, trans_price=0.00, other_dis=0.00, u_time = None):
    conn = getConnect()
    curs = initClientEncode(conn)
    sqlStr = '''INSERT INTO `prod_price_info` (`prod_id` ,`real_price` ,`cur_price` ,`m_price` ,`diff_price` ,`trans_price`,`other_dis` ,
                 `adwords` ,`coupon` ,`ex_gift` ,`order_cut` ,`crash_cut`,`u_time` ) VALUES (%s, %s, %s, %s, %s, %s,%s, %s, %s,%s, %s, %s,%s)'''
    #if isinstance(u_time, basestring):
    #    u_time = strptime(u_time,"%Y-%m-%d %H:%M:%S")
    param = [seEncode(pt) for pt in (prod_id, real_price, cur_price, m_price, diff_price, trans_price, other_dis, adwords, coupon, ex_gift, order_cut, crash_cut,u_time)]
    curs.execute(sqlStr, param)
    conn.commit()
    ret = curs.lastrowid
    curs.close()
    conn.close()
    return int(ret)

def batchSaveProdPriceInfo(conn, params):
    curs = initClientEncode(conn)
    sqlStr = '''INSERT INTO `prod_price_info` (`prod_id` ,`real_price` ,`cur_price` ,`m_price` ,`diff_price` ,`trans_price`,`other_dis` ,
                 `adwords` ,`coupon` ,`ex_gift` ,`order_cut` ,`crash_cut`,`u_time` ) VALUES (%s, %s, %s, %s, %s, %s,%s, %s, %s,%s, %s, %s,%s)'''
    wparams = list()
    for param in params:
        wparams.append([seEncode(pt) for pt in param])
    curs.executemany(sqlStr, wparams)
    conn.commit()
    curs.close()
    
def getProdPriceInfoFromProdId(conn, prod_id):
    curs = initClientEncode(conn)
    sqlStr = '''select real_price,cur_price,u_time,id from `prod_price_info` where prod_id=%s order by u_time DESC'''
    param = (prod_id)
    curs.execute(sqlStr, param)
    result = curs.fetchall()
    curs.close()
    if len(result) > 0:
        return result

def getMd5Key(src):
    m2 = hashlib.md5()
    m2.update(src)
    dest2 = int(m2.hexdigest(), 16)
    return dest2

def getCatKey(url):
    return str(getMd5Key(url))[0:16]

class SiteNameIDDict(object): 
    def __init__(self):
        pass
    def __new__(cls):
        '''
                    单态实现，初始化一次 
        '''
        if '_inst' not in vars(cls):
            cls.t_site_dict = dict(getAllWebsiteBaseInfo())
            cls._inst = super(SiteNameIDDict, cls).__new__(cls)
        return cls._inst
    
    def getSiteIdByName(self, siteName):
        return self.t_site_dict[siteName]

def getSiteIdByName(siteName):
    siteNameIDDict = SiteNameIDDict()
    return siteNameIDDict.getSiteIdByName(siteName)

class ProdCatIdDict(object): 
    def __init__(self):
        pass
    def __new__(cls):
        '''
                    单态实现，初始化一次 
        '''
        if '_inst' not in vars(cls):
            cls.t_cat_dict = dict()
            cls._inst = super(ProdCatIdDict, cls).__new__(cls)
        return cls._inst
    
    def __getKey(self, siteId, rawCatId):
        return '_'.join(map(str, (siteId, rawCatId)))
    
    def getProdCatId(self, siteId, catUrl):
        rawCatId = getCatKey(catUrl)
        key = self.__getKey(siteId, rawCatId)
        value = self.t_cat_dict.get(key, None)
        if value is None:
            value = getCatIdFromRawCatID(rawCatId, siteId)
            self.t_cat_dict[key] = value
        return value

def getCatIdFromRawInfo(siteId, catUrl):
    catIdDict = ProdCatIdDict()
    ret = catIdDict.getProdCatId(siteId, catUrl)
    if ret :
        return ret[0]

def getProdInfoRawIDMapId(siteId):
    return dict(getAllRawProdIdsBySite(siteId))#key:raw_id value:id

curModDir = os.path.dirname(os.path.abspath(__file__))
testFilePath = os.path.join(curModDir, 'test_resources')

def test(siteName, encoding='gb2312'):
    site_id = getSiteIdByName(siteName)
    fileName = os.path.join(testFilePath, '360buy_spider.log')
    conn = getConnect()
    params = list()
    i = 0
    prodIds = set([ t[0] for t in getAllRawProdIdsBySite(site_id)])
    print len(prodIds)
    import re
    p = re.compile('[0-9]+')
    p1 = re.compile(u'下单')
    fOutput = open('c:t360buy_jian.log', 'w')
    with open(fileName, 'r') as fInput:
        for line in fInput:
            line = line.strip().decode(encoding, 'ignore')
            ret = line.split('|')
            raw_id = ret[0]
            
            if p.search(ret[6]) != None and p1.search(ret[6]) != None:
                fOutput.write(ret[0] + '    ' + ret[6] + '\n') 
                
            #if getProdId(site_id,raw_id) != None:
            if raw_id in prodIds:
                #print '++++++++++++++++++++++++++'
                continue
            prodIds.add(raw_id)
            name = ret[3]
            repu = ret[4]
            eval_num = ret[5]
            url = ret[7]
            img_url = ret[8]
            catUrl = eval(ret[-1])[0]
            cat_id = getCatIdFromRawInfo(site_id, catUrl)
            u_time = ret[2]
            #print raw_id , name , url , repu, eval_num, img_url ,cat_id
            
            param = (site_id , raw_id , name , url , img_url , repu, eval_num, cat_id,u_time)
            if cat_id == '':
                print param
            params.append(param)
            i = i + 1
            if i == 100:
                batchSaveProdBaseInfo(conn, params)
                params = list()
                i = 0
    if i > 0:
        batchSaveProdBaseInfo(conn, params)
        del params      
    conn.close()
    fOutput.close()

def batchUpdateProdBaseInfo(conn, prodList):
    ''' 批量更新商品基本信息到表 table_name'''
    curs = initClientEncode(conn)
    sqlPattern = '''update prod_base_info_3c set repu=%s where id = %s'''
    #sqlStr = sqlPattern.format(table_name=table_name)
    wparams = list()
    for pBaseInfo in prodList:
        wparams.append([seEncode(pt) for pt in pBaseInfo])
    curs.executemany(sqlPattern, wparams)
    conn.commit()
    curs.close() 

    
if __name__ == '__main__':
    for t in match55bigoCats('笔记本'):
        print seEncode(t)
        
